package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import com.bw.bean.Sugar3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT b2.`name`,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_category3 b3 ON s.category3_id=b3.id \n" +
            "INNER JOIN base_category2 b2 ON b3.category2_id=b2.id\n" +
            "GROUP BY b2.`name`")
    List<Sugar> get1();

    @Select("SELECT b.`name`,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info s ON d.sku_id=s.id \n" +
            "INNER JOIN base_trademark b ON s.tm_id=b.id\n" +
            "GROUP BY b.`name` ORDER BY sum(o.final_total_amount) desc")
    List<Sugar> get2();

    @Select("CALL tt;")
    double get3();

    @Select("SELECT p.spu_name name,sum(o.final_total_amount) value FROM order_info o\n" +
            "INNER JOIN order_detail d ON o.id=d.order_id \n" +
            "INNER JOIN sku_info k ON d.sku_id=k.id \n" +
            "INNER JOIN spu_info p ON k.spu_id=p.id\n" +
            "GROUP BY p.spu_name")
    List<Sugar> get4();

    @Select("SELECT sum(amount) value FROM order_info WHERE create_time>='2023-01-06' and create_time<'2023-01-07'\n")
    double get5();

    @Select("call zz;")
    List<Sugar> get6();

    @Select("SELECT \n" +
            "b.org_name name,\n" +
            "sum(t.actual_distance) lc,\n" +
            "count(actual_distance) cs,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time)) time,\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(actual_distance) lv,\n" +
            "sum(t.actual_distance)/count(actual_distance) llv\n" +
            " FROM transport_task t\n" +
            "INNER JOIN line_base_info l ON t.line_id=l.id \n" +
            "INNER JOIN base_organ b ON l.org_id=b.id\n" +
            "GROUP BY b.org_name")
    List<Sugar2> get7();

    @Select("SELECT b2.name ,count(t.id) value FROM transport_task_detail t\n" +
            "INNER JOIN transport_task ON t.transport_task_id=transport_task.id \n" +
            "INNER JOIN line_base_info ON transport_task.line_id=line_base_info.id \n" +
            "INNER JOIN base_organ ON line_base_info.org_id=base_organ.id \n" +
            "INNER JOIN base_region_info b1 ON base_organ.region_id=b1.id \n" +
            "INNER JOIN base_region_info b2 ON b1.parent_id=b2.id\n" +
            "GROUP BY b2.`name`")
    List<Sugar> get8();

    @Select("SELECT \n" +
            "c.chapter_name name,\n" +
            "count(u.id) cs,\n" +
            "sum(u.position_sec) sc,\n" +
            "count(u.user_id) rs,\n" +
            "sum(u.position_sec)/count(u.user_id) lv\n" +
            " FROM user_chapter_process u \n" +
            "INNER JOIN chapter_info c ON u.chapter_id=c.id\n" +
            "GROUP BY c.chapter_name")
    List<Sugar3> get9();

    @Select("SELECT ll.leven name, count(*) value FROM\n" +
            "(SELECT CASE\n" +
            "WHEN score<60 THEN '[0,60)'\n" +
            "WHEN score>=60 and score<80 THEN '[60,80)'\n" +
            "WHEN score>=80 and score<90 THEN '[80,90)'\n" +
            "WHEN score>=90 and score<95 THEN '[90,95)'\n" +
            "WHEN score>=95 and score<=100 THEN '[95,100]'\n" +
            "end leven FROM test_exam) ll GROUP BY leven")
    List<Sugar> get10();

    @Select("SELECT b.`name`,sum(o.final_amount) value FROM order_info o\n" +
            "INNER JOIN base_province b ON o.province_id=b.id\n" +
            "GROUP BY b.`name`")
    List<Sugar> get11();

    @Select("SELECT sum(final_amount) value FROM order_info")
    double get12();
}
